﻿using MetroDataQuery.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MetroDataQuery.Utility
{
    public class ReviewMonitorPointsQuery
    {
        /// <summary>
        /// 按照给定的日期获取一组点的观测值
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <param name="dateStr"></param>
        /// <param name="isResult"></param>
        /// <returns></returns>
        public static List<MonitorPoints> GetPointsValueByTime(int prjId, string monitorType,
            string location, string dateStr)
        {
            var ptNamesStr = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            var ptNames = PointsDictionaryQuery.GetPointNames(prjId, monitorType, location);
            using (var db = new MetroProjectsEntities())
            {
                var sql = String.Format(@"select * from {0} where PrjId=@p0 
                                and DateTime=@p1 and Name in ({1})"
                                , "PreReviewMonitorPoints", ptNamesStr);
                var list = db.Database.SqlQuery<PreReviewMonitorPoints>(sql, prjId, dateStr).ToList();
                var finalResult = (from n in ptNames
                                   join p in list on n equals p.Name
                                       into gs
                                   from g in gs.DefaultIfEmpty()
                                   select new MonitorPoints()
                                   {
                                       DateTime = DateTime.Parse(dateStr),
                                       Name = n,
                                       PrjId = prjId,
                                       Value = g == null ? double.NaN : Math.Round(g.Value.Value, 4),
                                   }).ToList();
                return finalResult;
            }
        }


        public static List<DateTime?> GetMonitorTimeList(int prjId, string monitorType,
            string location = "", string endDateStr = "")
        {
            var ptNames = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            endDateStr = endDateStr == "" ? endDateStr = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd HH:mm:ss") : endDateStr;
            using (var db = new MetroProjectsEntities())
            {
                var count = 100;
                var sql = String.Format(@"select Top {0} DateTime from {1} where Name in ({2})
                            and PrjId=@p0 and DateTime<=@p1 group by DateTime  order by DateTime desc",
                    count, "PreReviewMonitorPoints", ptNames);
                var timeList = db.Database.SqlQuery<DateTime?>(sql, prjId, endDateStr);
                return timeList.ToList();
            }
        }


        /// <summary>
        /// 批准审核,将数据从审核数据表移动到结果数据表
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <param name="dateStr"></param>
        public static void AddReviewToResult(int prjId, string monitorType, string location, string dateStr)
        {
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, true);
            var names = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            using (var db = new MonitorProjectsEntities())
            {
                var deleteResult = string.Format(@"delete from {0} where DateTime=@p0 and PrjId=@p1 and Name in ({1})",
                    tableName, names);
                var moveSql = string.Format(@"insert into MonitorProjects.dbo.{0} (PrjId,DateTime,Name,Value) 
                    select PrjId,DateTime,Name,Value from MetroProjects.dbo.{1} where DateTime=@p0 and PrjId=@p1 and Name in ({2})",
                    tableName, "PreReviewMonitorPoints", names);
                var deleteReviewSql = string.Format(@"delete from MetroProjects.dbo.{0} where DateTime=@p0 and PrjId=@p1 and Name in ({1})",
                    "PreReviewMonitorPoints", names);

                db.Database.ExecuteSqlCommand(deleteResult, dateStr, prjId);
                db.Database.ExecuteSqlCommand(moveSql, dateStr, prjId);
                db.Database.ExecuteSqlCommand(deleteReviewSql, dateStr, prjId);
            }
        }


        /// <summary>
        /// 拒绝一个审核,删除该审核表中的数据
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="location"></param>
        /// <param name="dateStr"></param>
        public static void RemoveReviewResult(int prjId, string monitorType, string location, string dateStr)
        {
            var tableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, false);
            var resultTableName = MetroDataBaseUtility.GetTableNameByMonitorType(prjId, monitorType, true);
            var names = PointsDictionaryQuery.GetPointNamesIntoSqlArray(prjId, monitorType, location);
            using (var db = new MonitorProjectsEntities())
            {
                var deleteReviewSql = string.Format(@"delete from MetroProjects.dbo.{0} where DateTime=@p0 and PrjId=@p1 and Name in ({1})",
                    "PreReviewMonitorPoints", names);
                var deleteSql = string.Format(@"delete from {0} where DateTime=@p0 and PrjId=@p1 and Name in ({1})",
                    tableName, names);
                var deleteResultSql = string.Format(@"delete from {0} where DateTime=@p0 and PrjId=@p1 and Name in ({1})",
                  resultTableName, names);
                db.Database.ExecuteSqlCommand(deleteSql, dateStr, prjId);
                db.Database.ExecuteSqlCommand(deleteReviewSql, dateStr, prjId);
                db.Database.ExecuteSqlCommand(deleteResultSql, dateStr, prjId);
            }
        }
    }
}
